<?php
/*
 * Copyright 2008 by Oliver Radwan, Maxwell Palmer, Nolan McNair,
* Taylor Talmage, and Allen Tucker.  This program is part of RMH Homebase.
* RMH Homebase is free software.  It comes with absolutely no warranty.
* You can redistribute it and/or modify it under the terms of the GNU
* General Public License as published by the Free Software Foundation
* (see <http://www.gnu.org/licenses/ for more information).
*/
include_once('dbinfo.php');
/**
 * Functions to create, update, and retrieve information from the
 * dbSchedules table in the database.  This table contains the master
 * schedules for volunteers that are maintained by the House Manager.
 * These schedules provide the basis for generating each new month's
 * calendar, which is a series of shifts in the dbShifts table.
 * @version May 1, 2008
 * @author Allen Tucker and Maxwell Palmer
*/

/* Create an empty table of shifts for odd and even weeks and weekends
 * into which a volunteer can be scheduled.
* group = "One", "Two", or "Wkd"
* day = "Mon", "Tue", ... "Sun"
* time = "9-12", "12-15", "15-18", "18-21", "10-13", "13-16", "14-17, or "17-21"
* slots = the number of slots to be filled for this shift
* persons is a comma-separated list of ids, like "alex2071234567"
* notes = any notes that will be displayed for this shift on the schedule
*
*/
function setup_dbSchedules () {
	connect();
	mysql_query("DROP TABLE IF EXISTS dbSchedules");
	$result = mysql_query("CREATE TABLE dbSchedules (my_group TEXT, day TEXT, time TEXT, slots INT, persons TEXT, notes TEXT)");
	if (!$result)
		die ("Could not create dbSchedules table: " . mysql_error());
	$weekdays = array('Mon','Tue','Wed','Thu','Fri');
	$times = array('9-12','12-15','15-18','18-21');
	$prefix = "INSERT INTO dbSchedules VALUES (";
	foreach ($weekdays as $weekday) {
		foreach ($times as $time) {
			if ($time != "18-21") {   // don't generate 18-21 schedule for Family Room
				mysql_query($prefix . "'One', '" . $weekday . "','" . $time . "','" . 2 . "', '', '')");
				mysql_query($prefix . "'Two', '" . $weekday . "','" . $time . "','" . 2 . "', '', '')");
				mysql_query($prefix . "'FamOne', '" . $weekday . "','" . $time . "','" . 1 . "', '', '')");
				mysql_query($prefix . "'FamTwo', '" . $weekday . "','" . $time . "','" . 1 . "', '', '')");
			}
			else if ($weekday!="Fri"){
				mysql_query($prefix . "'One', '" . $weekday . "','" . $time . "','" . 3 . "', '', '')");
				mysql_query($prefix . "'Two', '" . $weekday . "','" . $time . "','" . 3 . "', '', '')");
			}
			else {
				mysql_query($prefix . "'One', '" . $weekday . "','" . "night" . "','" . 1 . "', '', '')");
				mysql_query($prefix . "'Two', '" . $weekday . "','" . "night" . "','" . 1 . "', '', '')");
			}
		}
	}
	for ($i=1; $i<=5; $i++) {
		$ithsat = "Sat" . $i;
		mysql_query($prefix . "'Wkd', '" . $ithsat . "', '10-13', 1," . "'', '')");
		mysql_query($prefix . "'Wkd', '" . $ithsat . "', '13-16', 1," . "'', '')");
		mysql_query($prefix . "'Wkd', '" . $ithsat . "', 'night', 1," . "'', '')");
	}
	for ($i=1; $i<=5; $i++) {
		$ithsun = "Sun" . $i;
		mysql_query($prefix . "'Wkd', '" . $ithsun . "', '9-12', 1," . "'', '')");
		mysql_query($prefix . "'Wkd', '" . $ithsun . "', '14-17', 1," . "'', '')");
		mysql_query($prefix . "'Wkd', '" . $ithsun . "', '17-21', 1," . "'', '')");
	}
	mysql_close();
}

/* schedule a person for a given day and time in group One, Two, or Wkd
 * update that person's schedule in the dbPersons table
*
*/
function schedule_person($group, $day, $time, $person_id){
	connect();
	$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
			$group . "' AND day = '" .
			$day . "' AND time = '" . $time . "'";
	$query2 = "SELECT * FROM dbPersons WHERE id = '" . $person_id . "'";
	$result = mysql_query($query1);
	$resultp = mysql_query($query2);
	if (!$result || !$resultp) die ("could not query the database");
	// be sure the person exists and is scheduled
	if (mysql_num_rows($result) !== 1 || mysql_num_rows($resultp) !== 1) {
		mysql_close();
		return "Error: group-day-time not valid or person not in database";
	}
	$result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	$resultp_row = mysql_fetch_array($resultp, MYSQL_ASSOC);
	$persons = explode(',', $result_row['persons']);    // get an array of scheduled person id's
	$schedule = explode(',', $resultp_row['schedule']); // get an array of person's scheduled times
	$availability = explode(',', $resultp_row['availability']);     // and their availabiltiy
	if (in_array(substr($day,0,3).$time, $availability) &&
			!in_array($person_id, $persons) &&
			!in_array($group.substr($day,0,3).$time, $schedule)) {
		$persons[] = $person_id;             // add the person to the array, and
		$schedule[] = $group . $day . $time; // add the time to the person's schedule
		$result_row['persons'] = implode(',', $persons);     // and update one row in each table
		$resultp_row['schedule'] = implode(',', $schedule);  // in the database
		mysql_query("UPDATE dbSchedules SET persons = '". $result_row['persons'] . "' WHERE my_group = '" .
				$group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
				mysql_query("UPDATE dbPersons SET schedule = '" . $resultp_row['schedule'] .
				"' WHERE id = '" . $person_id . "'");
				mysql_close();
				return "";
	}
	mysql_close();
	return "Error: can't schedule a person not available or already scheduled";
}
/* unschedule a volunteer from group One, Two, or Wkd at a given day and time
 * update that person's schedule in the dbPersons table
*
*/
function unschedule_person ($group, $day, $time, $person_id) {
	connect();
	$query = "SELECT * FROM dbSchedules WHERE my_group = '" .
			$group . "' AND day = '" .
			$day . "' AND time = '" . $time . "'";
	$queryp = "SELECT * FROM dbPersons WHERE id = '" . $person_id . "'";
	$result = mysql_query($query);
	$resultp = mysql_query($queryp);
	// be sure the person exists and is scheduled
	if (!$result || mysql_num_rows($result) !== 1) {
		mysql_close();
		return "Error: group-day-time not valid";
	}
	else if (!$resultp || mysql_num_rows($resultp) !== 1) {
		$result_row = mysql_fetch_array($result, MYSQL_ASSOC);
		$persons = explode(',', $result_row['persons']);    // get an array of scheduled person id's
		if (in_array($person_id, $persons)) {
			$index = array_search($person_id, $persons);
			array_splice($persons, $index, 1);               // remove the person from the array, and
			$result_row['persons'] = implode(',', $persons); // and update one row in the schedule
			mysql_query("UPDATE dbSchedules SET persons = '". $result_row['persons'] . "' WHERE my_group = '" .
					$group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
		}
		mysql_close();
		return "Error: person not in database";
	}
	$result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	$resultp_row = mysql_fetch_array($resultp, MYSQL_ASSOC);
	$persons = explode(',', $result_row['persons']);    // get an array of scheduled person id's
	$schedule = explode(',', $resultp_row['schedule']); // get an array of person's scheduled times
	if (in_array($person_id, $persons) && in_array($group.$day.$time, $schedule)) {
		$index = array_search($person_id, $persons);
		$indexp = array_search($group.$day.$time, $schedule);
		array_splice($persons, $index, 1);   // remove the person from the array, and
		array_splice($schedule, $indexp, 1); // remove the time from the person's schedule
		$result_row['persons'] = implode(',', $persons);     // and update one row in each table
		$resultp_row['schedule'] = implode(',', $schedule);  // in the database
		mysql_query("UPDATE dbSchedules SET persons = '". $result_row['persons'] . "' WHERE my_group = '" .
				$group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
				mysql_query("UPDATE dbPersons SET schedule = '" . $resultp_row['schedule'] .
				"' WHERE id = '" . $person_id . "'");
				mysql_close();
				return "";
	}
	mysql_close();
	return "Error: can't unschedule a person not scheduled";
}
/* insert a note in the scedule for a given group, day, and time.
 *
*/
function make_notes ($group, $day, $time, $notes) {
	connect();
	$query = "SELECT * FROM dbSchedules WHERE my_group = '" .
			$group . "' AND day = '" .
			$day . "' AND time = '" . $time . "'";
	$result = mysql_query($query);
	if (!$result) die ("could not query the database");
	// be sure the person exists and is scheduled
	if (mysql_num_rows($result) !== 1) {
		mysql_close();
		return "Error: group-day-time not valid";
	}
	$result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	$result_row['notes'] = $notes;
	mysql_query("UPDATE dbSchedules SET notes = '". $result_row['notes'] . "' WHERE my_group = '" .
			$group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
			mysql_close();
			return "";
}
/*
 * @return whether or not a person is scheduled in a given group, day and time
*
*/
function is_scheduled ($group, $day, $time, $person_id) {
	connect();
	$query = "SELECT * FROM dbSchedules WHERE my_group = '" .
			$group . "' AND day = '" .
			$day . "' AND time = '" . $time . "'";
	$result = mysql_query($query);
	if (!$result) die ("could not query the database");
	if (mysql_num_rows($result) !== 1) {
		mysql_close();
		return "Error: group-day-time not valid";
	}
	$result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	$persons = explode(',', $result_row['persons']);    // get array of scheduled person id's
	mysql_close();
	if (in_array($person_id, $persons))
		return true;
	else return false;
}
/*
 * @return all persons scheduled for a particular group, day, and time
* as an array of associative arrays.  Each associative array has
* entries indexed by the field names of a person in dbPersons.
*/
function get_persons($group, $day, $time) {
	connect();
	$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
			$group . "' AND day = '" .
			$day . "' AND time = '" . $time . "'";
	$result = mysql_query($query1);
	if (!$result) die ("could not query the database");
	$out = array();
	if (mysql_num_rows($result) !== 1) {
		mysql_close();
		$out[] = "Error: group-day-time not valid";
		return $out;
	}
	$result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	$person_ids = explode(',', $result_row['persons']);    // get an array of scheduled person id's
	foreach ($person_ids as $person_id)
		if ($person_id!="") {
		$query2 = "SELECT * FROM dbPersons WHERE id = '" . $person_id . "'";
		$resultp = mysql_query($query2);
		if (!$resultp) die ("could not query the database");
		if (mysql_num_rows($resultp) !== 1) {
			mysql_close();
			$out[] = $person_id;
			return $out;
		}
		$out[] = mysql_fetch_array($resultp, MYSQL_ASSOC);
	}
	mysql_close();
	return $out;
}
/*
 * @return ids of all persons scheduled for a particular group, day, and time
*/
function get_person_ids($group, $day, $time) {
	connect();
	$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
			$group . "' AND day = '" .
			$day . "' AND time = '" . $time . "'";
	$result = mysql_query($query1);
	if (!$result) die ("could not query the database");
	if (mysql_num_rows($result) !== 1) {
		mysql_close();
		return array("Error: group-day-time not valid");
	}
	$result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	$person_ids = explode(',', $result_row['persons']);
	mysql_close();
	return $person_ids;
}
/*
 * @return number of slots for a particular group, day, and time
*/
function get_total_slots($group, $day, $time) {
	connect();
	$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
			$group . "' AND day = '" .
			$day . "' AND time = '" . $time . "'";
	$result = mysql_query($query1);
	if (!$result) die ("could not query the database");
	if (mysql_num_rows($result) !== 1) {
		mysql_close();
		return "Error: group-day-time not valid";
	}
	$result_row = mysql_fetch_array($result, MYSQL_ASSOC);
	mysql_close();
	return $result_row['slots'];
}
/*
 * @return number of vacancies for a particular group, day, and time
*/
function get_total_vacancies($group,$day,$time) {
	$slots=get_total_slots($group,$day,$time);
	$persons=count(get_persons($group,$day,$time));
	return $slots-$persons;
}
/*
 * @return number of vacancies for a particular group, day, and time
*/
function check_valid_schedule($group, $day, $time) {
	connect();
	$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
			$group . "' AND day = '" .
			$day . "' AND time = '" . $time . "'";
	$result = mysql_query($query1);
	mysql_close();
	if (!$result) die ("could not query the database");
	if (mysql_num_rows($result) !== 1) {
		return false;
	}
	return true;
}
/*
 * @return number of vacancies for a particular group, day, and time
*/
function edit_schedule_vacancy($group,$day,$time,$change) {
	connect();
	$query1 = "SELECT * FROM dbSchedules WHERE my_group = '" .
			$group . "' AND day = '" .
			$day . "' AND time = '" . $time . "'";
	$result = mysql_query($query1);
	if (!$result) die ("could not query the database");
	if (mysql_num_rows($result) !== 1) {
		mysql_close();
		return false;
	}
	$result_row=mysql_fetch_array($result,MYSQL_ASSOC);
	$result_row['slots']=$result_row['slots']+$change;
	mysql_query("UPDATE dbSchedules SET slots = '". $result_row['slots'] . "' WHERE my_group = '" .
			$group . "' AND day = '" . $day . "' AND time = '" . $time . "'");
		 mysql_close();
		 return true;
}
/*
 * @return master schedule as a big associative array
*/
function get_dbSchedules() {
	connect();
	$query="SELECT * FROM dbSchedules";
	$result = mysql_query($query);
	if (!$result)
		die ("Could not query the database: <br />" . mysql_error());
	$outcome = array();
	for($i=0;$i<mysql_num_rows($result);$i++) {
		$outcome[] = mysql_fetch_array($result, MYSQL_ASSOC);
	}
	mysql_close();
	return $outcome;
}

?>
